package com.ldj.struts.action;

import java.io.File;
import java.io.IOException;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.Hashtable;
import java.util.List;
import java.util.Vector;
import java.util.regex.Pattern;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import jxl.Cell;
import jxl.Sheet;
import jxl.Workbook;
import jxl.read.biff.BiffException;
import jxl.write.WriteException;

import org.apache.struts.action.ActionForm;
import org.apache.struts.action.ActionForward;
import org.apache.struts.action.ActionMapping;
import org.apache.struts.actions.DispatchAction;

import com.ldj.common.Upload;
import com.ldj.hibernate.DataDictionary;
import com.ldj.hibernate.DataDictionaryDAO;
import com.ldj.hibernate.EmployerInfo;
import com.ldj.hibernate.EmployerInfoDAO;
import com.ldj.hibernate.UnitUser;
import com.ldj.hibernate.UnitUserDAO;
import com.ldj.struts.form.EmployerInfoForm;

public class EmployerInfoExcelAction extends DispatchAction {

	public ActionForward list(ActionMapping mapping, ActionForm form,
			HttpServletRequest request, HttpServletResponse response) {
		EmployerInfoForm eForm = (EmployerInfoForm)form;
		EmployerInfoDAO dao = new EmployerInfoDAO(request, response);
		List list = dao.findExcelAllPage(eForm, null);
		request.setAttribute("list", list);
		return mapping.findForward("list");
	}
	
	public ActionForward detail(ActionMapping mapping, ActionForm form,
			HttpServletRequest request, HttpServletResponse response) {
		EmployerInfoDAO dao = new EmployerInfoDAO(request, response);
		EmployerInfo info = dao.findById(request.getParameter("eid"));
		request.setAttribute("eInfo", info);
		return mapping.findForward("detail");
	}
	
	
	//用人单位导入
	public ActionForward upload(ActionMapping mapping, ActionForm form,
			HttpServletRequest request, HttpServletResponse response) {
		String code = request.getParameter("code");
		try {
			HashMap map = Upload.upload(request, "employerInfo", ".xls");
			if(map.containsKey("fileName")){
				return new ActionForward("/EmployerInfoExcel.do?method=insertExcel&path=" + (String)map.get("fileName"), true);
			}else{
				return new ActionForward("/EmployerInfoExcel.do?method=insertExcel", true);
			}
		} catch (IOException e) {
			e.printStackTrace();
		}
		return null;
	}
	
	public ActionForward insertExcel(ActionMapping mapping, ActionForm form,
			HttpServletRequest request, HttpServletResponse response) throws WriteException, SQLException, IOException{
		List list = new Vector();
		Workbook book = null;
		boolean t = true;
		DataDictionaryDAO ddDAO = new DataDictionaryDAO(request,response);
		EmployerInfoDAO dao = new EmployerInfoDAO(request,response);
		//将所有的码表信息放入Hashtable中
		Hashtable ht=new Hashtable();
		List<DataDictionary> dList = ddDAO.findAll("001","009","002");
		for(int i=0;i<dList.size();i++){
			DataDictionary dd = dList.get(i);
			ht.put(dd.getDataDictionaryName(), dd.getDataDictionaryCode());
		}
		String name=request.getParameter("path");
		if(name == null || "".equals(name)){
			request.setAttribute("error", "文件上传失败，请选择正确的文件格式上传！");
			return mapping.findForward("error");
		}else{
			name = request.getRealPath("/employerInfo/" + name);
		}
		File file = new File(name);
    	if(!file.exists()){
    		request.setAttribute("error", "文件上传失败，请选择正确的文件格式上传！");
			return mapping.findForward("error");
    	}
    	long userNum = System.currentTimeMillis();
    	try {
			//打开文件
    		book = Workbook.getWorkbook(new File(name));
			//获得第一个表的工作对象，“0”表示第一个表
			Sheet sheet = book.getSheet(0);
			int num = sheet.getRows();
			//得到第一列，第一行的单元格（0，0）
		//	ArrayList al = new ArrayList();
			for(int i =1; i < num; i ++){
				Cell cell0 = sheet.getCell(0,i);
				Cell cell1 = sheet.getCell(1,i);
				Cell cell2 = sheet.getCell(2,i);
				Cell cell3 = sheet.getCell(3,i);
				Cell cell4 = sheet.getCell(4,i);
				Cell cell5 = sheet.getCell(5,i);
				Cell cell6 = sheet.getCell(6,i);
				Cell cell7 = sheet.getCell(7,i);
				Cell cell8 = sheet.getCell(8,i);
				Cell cell9 = sheet.getCell(9,i);
				Cell cell10 = sheet.getCell(10,i);
				Cell cell11 = sheet.getCell(11,i);
				Cell cell12 = sheet.getCell(12,i);
				Cell cell13 = sheet.getCell(13,i);
				Cell cell14 = sheet.getCell(14,i);
				Cell cell15 = sheet.getCell(15,i);
				Cell cell16 = sheet.getCell(16,i);
				Cell cell17 = sheet.getCell(17,i);
				Cell cell18 = sheet.getCell(18,i);
				Cell cell19 = sheet.getCell(19,i);
				Cell cell20 = sheet.getCell(20,i);
				Cell cell21 = sheet.getCell(21,i);
				Cell cell22 = sheet.getCell(22,i);
				Cell cell23 = sheet.getCell(23,i);
				Cell cell24 = sheet.getCell(24,i);
				Cell cell25 = sheet.getCell(25,i);
				Cell cell26 = sheet.getCell(26,i);
				Cell cell27 = sheet.getCell(27,i);
				Cell cell28 = sheet.getCell(28,i);
				Cell cell29 = sheet.getCell(29,i);
				Cell cell30 = sheet.getCell(30,i);
				Cell cell31 = sheet.getCell(31,i);
				Cell cell32 = sheet.getCell(32,i);
				Cell cell33 = sheet.getCell(33,i);
				Cell cell34 = sheet.getCell(34,i);
				Cell cell35 = sheet.getCell(35,i);
				Cell cell36 = sheet.getCell(36,i);
				Cell cell37 = sheet.getCell(37,i);
				Cell cell38 = sheet.getCell(38,i);
				Cell cell39 = sheet.getCell(39,i);
				
				String empInfoName = cell0.getContents();
				String empInfoCode = cell1.getContents();
				String empInfoBankAccount = cell2.getContents();
				String empInfoBankOfDeposit = cell3.getContents();
				String empInfoLandtaxUrl =cell4.getContents();
				String empInfoLandtaxRegisOrgan =cell5.getContents();
				String empInfoShopUrl =cell6.getContents();
				String empInfoShopCard =cell7.getContents();
				String empInfoBusinessRegisOrgan =cell8.getContents();
				String empInfoUnitType =cell9.getContents();
				String empInfoIndustry =cell10.getContents();
				String empInfoPlaceOrgan =cell11.getContents();
				String empInfoPlaceAddress =cell12.getContents();
				String empInfoCorporateRepresent =cell13.getContents();
				String empInfoRepresentIdNum =cell14.getContents();
				String empInfoCellPhone =cell15.getContents();
				String empInfoTelphone =cell16.getContents();
				String empInfoLabManaPrincipal =cell17.getContents();
				String empInfoCellPhone1 =cell18.getContents();
				String empInfoTelphone1 =cell19.getContents();
				String empInfoManHourRegu =cell20.getContents();
				String empInfoLowsalaLevel =cell21.getContents();
				String empInfoAllWorkNum =cell22.getContents();
				String empInfoTownWorkNum =cell23.getContents();
				String empInfoCountryWorkNum =cell24.getContents();
				String empInfoManNum =cell25.getContents();
				String empInfoWomanNum =cell26.getContents();
				String empInfoContractedNum =cell27.getContents();
				String empInfoNcontractedNum =cell28.getContents();
				String empInfoGoonContractNum =cell29.getContents();
				String empInfoEmpInjInsNum =cell30.getContents();
				String empInfoEndowmentInsNum =cell31.getContents();
				String empInfoMedicalInsNum =cell32.getContents();
				String empInfoUnempInsNum =cell33.getContents();
				String empInfoMaterityInsNum =cell34.getContents();
				String empInfoNoInsNum =cell35.getContents();
				String empInfoIfOrgDealDisputed =cell36.getContents();
				String empInfoIfContract =cell37.getContents();
				String empInfoHonesLevel =cell38.getContents();
				String empInfoDelOper =cell39.getContents();
				
				if(empInfoName==null || "".equals(empInfoName)){
					request.setAttribute("error", "第"+(i+1)+"行，单位名称不能为空!");
					t = false;
					break;
				}
				
				EmployerInfo info = new EmployerInfo();
				info.setEmpInfoName(empInfoName);
				info.setEmpInfoCode(empInfoCode);
				info.setEmpInfoBankAccount(empInfoBankAccount);
				info.setEmpInfoBankOfDeposit(empInfoBankOfDeposit);
				info.setEmpInfoLandtaxRegisOrgan(empInfoLandtaxRegisOrgan);
				info.setEmpInfoShopCard(empInfoShopCard);
				info.setEmpInfoBusinessRegisOrgan(empInfoBusinessRegisOrgan);
				info.setEmpInfoPlaceAddress(empInfoPlaceAddress);
				info.setEmpInfoCorporateRepresent(empInfoCorporateRepresent);
				info.setEmpInfoRepresentIdNum(empInfoRepresentIdNum);
				info.setEmpInfoCellPhone(empInfoCellPhone);
				info.setEmpInfoTelphone(empInfoTelphone);
				info.setEmpInfoLabManaPrincipal(empInfoLabManaPrincipal);
				info.setEmpInfoCellPhone1(empInfoCellPhone1);
				info.setEmpInfoTelphone1(empInfoTelphone1);
				info.setEmpInfoManHourRegu(empInfoManHourRegu);
				info.setEmpInfoLowsalaLevel(empInfoLowsalaLevel);
				if(empInfoUnitType != null && !"".equals(empInfoUnitType)){
					if(ht.containsKey(empInfoUnitType)){
						String empInfoUnitCode = (String)ht.get(empInfoUnitType);
						info.setEmpInfoUnitType(empInfoUnitCode);
					}
					else{
							request.setAttribute("error", "第"+(i+1)+"行，单位类型不存在!");
							t = false;
							break;
					}
				}
				if(empInfoIndustry != null && !"".equals(empInfoIndustry)){
					if(ht.containsKey(empInfoIndustry)){
						String empInfoIndustryCode = (String)ht.get(empInfoIndustry);
						info.setEmpInfoIndustry(empInfoIndustryCode);
					}
					else{
							request.setAttribute("error", "第"+(i+1)+"行，所属行业不存在!");
							t = false;
							break;
					}
				}
				
				if(empInfoPlaceOrgan != null && !"".equals(empInfoPlaceOrgan)){
					if(ht.containsKey(empInfoPlaceOrgan)){
						String empInfoPlaceCode = (String)ht.get(empInfoPlaceOrgan);
						info.setEmpInfoPlaceOrgan(empInfoPlaceCode);
					}
					else{
							request.setAttribute("error", "第"+(i+1)+"行，所属地区不存在!");
							t = false;
							break;
					}
				}
				else{
					request.setAttribute("error", "第"+(i+1)+"行，所属地区不能为空!");
					t = false;
					break;
				}
				if(empInfoIfOrgDealDisputed!=null && !"".equals(empInfoIfOrgDealDisputed)){
					if( !"是".equals(empInfoIfOrgDealDisputed) && !"否".equals(empInfoIfOrgDealDisputed)){
						request.setAttribute("error", "第"+(i+1)+"行，是否成立劳动监察组列只能输入是或否!");
						t = false;
						break;
					}
				}
				if(empInfoIfOrgDealDisputed !=null && "是".equals(empInfoIfOrgDealDisputed)){
					info.setEmpInfoIfOrgDealDisputed("0");
				}
				
				if(empInfoIfOrgDealDisputed !=null && "否".equals(empInfoIfOrgDealDisputed)){
					info.setEmpInfoIfOrgDealDisputed("1");
				}
				
				if(empInfoIfContract!=null && !"".equals(empInfoIfContract)){
					if( !"是".equals(empInfoIfContract) && !"否".equals(empInfoIfContract)){
						request.setAttribute("error", "第"+(i+1)+"行，是否签订劳动合同列只能输入是或否!");
						t = false;
						break;
					}
				}
				
				if(empInfoIfContract !=null && "是".equals(empInfoIfContract)){
					info.setEmpInfoIfContract("0");
				}
				
				if(empInfoIfContract !=null && "否".equals(empInfoIfContract)){
					info.setEmpInfoIfContract("1");
				}
				if(empInfoHonesLevel!=null && !"".equals(empInfoHonesLevel)){
					if( !"A".equals(empInfoHonesLevel) && !"B".equals(empInfoHonesLevel) && !"C".equals(empInfoHonesLevel) && !"D".equals(empInfoHonesLevel)){
						request.setAttribute("error", "第"+(i+1)+"行，诚信等级列只能为A、B、C、D等级中的一个!");
						return mapping.findForward("error");
					}
				}
				
				/*if( !"0".equals(empInfoDelOper) && "1".equals(empInfoDelOper)){
					request.setAttribute("error", "第"+(i+1)+"行，删除标志列只能为0或1!");
					t = false;
					break;
				}*/
				if(empInfoAllWorkNum==null || "".equals(empInfoAllWorkNum)){
					empInfoAllWorkNum="0";
				}
				if(empInfoTownWorkNum==null || "".equals(empInfoTownWorkNum)){
					empInfoTownWorkNum="0";
				}
				if(empInfoCountryWorkNum==null || "".equals(empInfoCountryWorkNum)){
					empInfoCountryWorkNum="0";
				}
				if(empInfoManNum==null || "".equals(empInfoManNum)){
					empInfoManNum="0";
				}
				if(empInfoWomanNum==null || "".equals(empInfoWomanNum)){
					empInfoWomanNum="0";
				}
				if(empInfoContractedNum==null || "".equals(empInfoContractedNum)){
					empInfoContractedNum="0";
				}
				if(empInfoNcontractedNum==null || "".equals(empInfoNcontractedNum)){
					empInfoNcontractedNum="0";
				}
				if(empInfoGoonContractNum==null || "".equals(empInfoGoonContractNum)){
					empInfoGoonContractNum="0";
				}
				if(empInfoEmpInjInsNum==null || "".equals(empInfoEmpInjInsNum)){
					empInfoEmpInjInsNum="0";
				}
				if(empInfoEndowmentInsNum==null || "".equals(empInfoEndowmentInsNum)){
					empInfoEndowmentInsNum="0";
				}
				if(empInfoMedicalInsNum==null || "".equals(empInfoMedicalInsNum)){
					empInfoMedicalInsNum="0";
				}
				if(empInfoUnempInsNum==null || "".equals(empInfoUnempInsNum)){
					empInfoUnempInsNum="0";
				}
				if(empInfoMaterityInsNum==null || "".equals(empInfoMaterityInsNum)){
					empInfoMaterityInsNum="0";
				}
				if(empInfoNoInsNum==null || "".equals(empInfoNoInsNum)){
					empInfoNoInsNum="0";
				}
				 Pattern pattern = Pattern.compile("[0-9]*"); 
				 if(!(pattern.matcher(empInfoAllWorkNum).matches())){
					 request.setAttribute("error", "第"+(i+1)+"行，员工总人数应为数字!");
						t = false;
						break;
				 }
				 
				 if(!(pattern.matcher(empInfoTownWorkNum).matches())){
					 request.setAttribute("error", "第"+(i+1)+"行，城镇职工人数应为数字!");
						t = false;
						break;
				 }
				 if(!(pattern.matcher(empInfoCountryWorkNum).matches())){
					 request.setAttribute("error", "第"+(i+1)+"行，农民工人数应为数字!");
						t = false;
						break;
				 }
				 if(!(pattern.matcher(empInfoManNum).matches())){
					 request.setAttribute("error", "第"+(i+1)+"行，男职工人数应为数字!");
						t = false;
						break;
				 }
				 if(!(pattern.matcher(empInfoWomanNum).matches())){
					 request.setAttribute("error", "第"+(i+1)+"行，女职工人数应为数字!");
						t = false;
						break;
				 }
				 if(!(pattern.matcher(empInfoContractedNum).matches())){
					 request.setAttribute("error", "第"+(i+1)+"行，已签合同人数应为数字!");
						t = false;
						break;
				 }
				 if(!(pattern.matcher(empInfoNcontractedNum).matches())){
					 request.setAttribute("error", "第"+(i+1)+"行，未签合同人数应为数字!");
						t = false;
						break;
				 }
				 if(!(pattern.matcher(empInfoGoonContractNum).matches())){
					 request.setAttribute("error", "第"+(i+1)+"行，当年续签合同人数应为数字!");
						t = false;
						break;
				 }
				 if(!(pattern.matcher(empInfoEmpInjInsNum).matches())){
					 request.setAttribute("error", "第"+(i+1)+"行，工伤保险人数应为数字!");
						t = false;
						break;
				 }
				 if(!(pattern.matcher(empInfoEndowmentInsNum).matches())){
					 request.setAttribute("error", "第"+(i+1)+"行，养老保险人数应为数字!");
						t = false;
						break;
				 }
				 if(!(pattern.matcher(empInfoMedicalInsNum).matches())){
					 request.setAttribute("error", "第"+(i+1)+"行，医疗保险人数应为数字!");
						t = false;
						break;
				 }
				 if(!(pattern.matcher(empInfoUnempInsNum).matches())){
					 request.setAttribute("error", "第"+(i+1)+"行，失业保险人数应为数字!");
						t = false;
						break;
				 }
				 if(!(pattern.matcher(empInfoMaterityInsNum).matches())){
					 request.setAttribute("error", "第"+(i+1)+"行，生育保险人数应为数字!");
						t = false;
						break;
				 }
				 if(!(pattern.matcher(empInfoNoInsNum).matches())){
					 request.setAttribute("error", "第"+(i+1)+"行，未参保人数应为数字!");
						t = false;
						break;
				 }
				 if(Integer.parseInt(empInfoAllWorkNum)<Integer.parseInt(empInfoTownWorkNum)+Integer.parseInt(empInfoCountryWorkNum)){
					 request.setAttribute("error", "第"+(i+1)+"行，员工总人数应大于等于城镇职工人数与农民工人数的总和!");
					 t = false;
						break;
				 }
				 if(Integer.parseInt(empInfoAllWorkNum)<Integer.parseInt(empInfoManNum)+Integer.parseInt(empInfoWomanNum)){
					 request.setAttribute("error", "第"+(i+1)+"行，员工总人数应大于等于男职工人数与女职工人数的总和!");
					 t = false;
						break;
				 }
				 if(Integer.parseInt(empInfoAllWorkNum)<Integer.parseInt(empInfoContractedNum)+Integer.parseInt(empInfoNcontractedNum)){
					 request.setAttribute("error", "第"+(i+1)+"行，员工总人数应大于等于已签合同人数与未签订合同人数的总和!");
					 t = false;
						break;
				 }
				 if(Integer.parseInt(empInfoAllWorkNum)<Integer.parseInt(empInfoGoonContractNum)){
					 request.setAttribute("error", "第"+(i+1)+"行，员工总人数应大于等于当年续签合同人数!");
					 t = false;
						break;
				 }
				 if(Integer.parseInt(empInfoAllWorkNum)<Integer.parseInt(empInfoEmpInjInsNum)){
					 request.setAttribute("error", "第"+(i+1)+"行，员工总人数应大于等于工伤保险人数!");
					 t = false;
						break;
				 }
				 if(Integer.parseInt(empInfoAllWorkNum)<Integer.parseInt(empInfoEndowmentInsNum)){
					 request.setAttribute("error", "第"+(i+1)+"行，员工总人数应大于等于养老保险人数!");
					 t = false;
						break;
				 }
				 if(Integer.parseInt(empInfoAllWorkNum)<Integer.parseInt(empInfoMedicalInsNum)){
					 request.setAttribute("error", "第"+(i+1)+"行，员工总人数应大于等于医疗保险人数!");
					 t = false;
						break;
				 }
				 if(Integer.parseInt(empInfoAllWorkNum)<Integer.parseInt(empInfoUnempInsNum)){
					 request.setAttribute("error", "第"+(i+1)+"行，员工总人数应大于等于失业保险人数!");
					 t = false;
						break;
				 }
				 if(Integer.parseInt(empInfoAllWorkNum)<Integer.parseInt(empInfoMaterityInsNum)){
					 request.setAttribute("error", "第"+(i+1)+"行，员工总人数应大于等于生育保险人数!");
					 t = false;
						break;
				 }
				 if(Integer.parseInt(empInfoAllWorkNum)<Integer.parseInt(empInfoNoInsNum)){
					 request.setAttribute("error", "第"+(i+1)+"行，员工总人数应大于等于未参保人数!");
					 t = false;
						break;
				 }
				info.setEmpInfoAllWorkNum(empInfoAllWorkNum);
				info.setEmpInfoTownWorkNum(empInfoTownWorkNum);
				info.setEmpInfoCountryWorkNum(empInfoCountryWorkNum);
				info.setEmpInfoManNum(empInfoManNum);
				info.setEmpInfoWomanNum(empInfoWomanNum);
				info.setEmpInfoContractedNum(empInfoContractedNum);
				info.setEmpInfoNcontractedNum(empInfoNcontractedNum);
				info.setEmpInfoGoonContractNum(empInfoGoonContractNum);
				info.setEmpInfoEmpInjInsNum(empInfoEmpInjInsNum);
				info.setEmpInfoEndowmentInsNum(empInfoEndowmentInsNum);
				info.setEmpInfoMedicalInsNum(empInfoMedicalInsNum);
				info.setEmpInfoUnempInsNum(empInfoUnempInsNum);
				info.setEmpInfoMaterityInsNum(empInfoMaterityInsNum);
				info.setEmpInfoNoInsNum(empInfoNoInsNum);
				
				info.setEmpInfoHonesLevel(empInfoHonesLevel);
				info.setEmpInfoDelOper("0");
				
				list.add(info);
			}
		} catch (IOException e) {
			request.setAttribute("error", "读取导入文件时发生错误!");
			e.printStackTrace();
		} catch (BiffException e) {
			// TODO Auto-generated catch block
			request.setAttribute("error", "读取导入文件时发生错误!");
			e.printStackTrace();
		}finally{
			if(book !=null)
			 book.close();
		}
		if(t){
			UnitUserDAO uDAO = new UnitUserDAO(request, response);
			for(int j = 0; j < list.size(); j ++){
				EmployerInfo eInfo =(EmployerInfo)list.get(j);
				dao.save(eInfo);
				//产生相应的用户信息
				UnitUser info = new UnitUser();
				info.setUnitUser(eInfo);
				userNum = userNum + 1;
				info.setUnitUserLoginName("zy" + userNum);
				info.setUnitUserPassword("123456");
				info.setUnitUserName("zy" + userNum);
				info.setUnitUserSex("0");
				info.setUnitUserDepart("");
				info.setUnitUserDuty("");
				uDAO.save(info);
			}
		}
    	return mapping.findForward("error");
	}
	
	public ActionForward addUser(ActionMapping mapping, ActionForm form,
			HttpServletRequest request, HttpServletResponse response) {
		EmployerInfoDAO dao = new EmployerInfoDAO(request, response);
		UnitUserDAO uDAO = new UnitUserDAO(request, response);
		List<EmployerInfo> eList =dao.findNotUser();
		long userNum = System.currentTimeMillis();
		int i=0;
		for(;i<eList.size();i++){
			EmployerInfo eInfo = eList.get(i);
			UnitUser info = new UnitUser();
			info.setUnitUser(eInfo);
			userNum = userNum + 1;
			info.setUnitUserLoginName("zy" + userNum);
			info.setUnitUserPassword("123456");
			info.setUnitUserName("zy" + userNum);
			info.setUnitUserSex("0");
			info.setUnitUserDepart("");
			info.setUnitUserDuty("");
			uDAO.save(info);
		}
		request.setAttribute("error1", "分配成功，共分配"+i+"个用户!");
		return mapping.findForward("errorEmployer");
	}
}